package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import pojo.AccuracyLevels;

public class AccuracyLevelsDao {

    private Connection c;
    public AccuracyLevelsDao(Connection c) {
        this.c = c;
    }
    
    //add new Accuracy Level
    public void add(AccuracyLevels aculevel) {
        try {
            PreparedStatement pstmt = c.prepareStatement("insert into accuracy_levels values(?,?,?,?,?,?,?,?,?,?)");
            pstmt.setInt(1, aculevel.getAlId());
            pstmt.setInt(2, aculevel.getWidthBy());
            pstmt.setInt(3, aculevel.getPagesBy());
            pstmt.setInt(4, aculevel.getDepthBy());
            pstmt.setInt(5, aculevel.getLengthBy());
            pstmt.setInt(6, aculevel.getWeightBy());
            pstmt.setInt(7, aculevel.getFormatBy());
            pstmt.setInt(8, aculevel.getMailFormatBy());
            pstmt.setInt(9, aculevel.getItemBy());
            pstmt.setInt(10, aculevel.getProdCodeBy());
            pstmt.execute();
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }
    }

    //delete Accuracy Level
    public void delete(AccuracyLevels aculevel) {
        try {
            PreparedStatement pstmt = c.prepareStatement("delete from accuracy_levels where al_id=?");
            pstmt.setInt(1, aculevel.getAlId());
            pstmt.execute();
        } catch (SQLException ex1) {
            ex1.printStackTrace();
        }
    }

    //method to retreive by accuracy
    public Vector<Integer> getbyaccuracylevel(String param,int acclevel)
    {
        System.out.println("Accuracy Level is equal--------"+acclevel);
        System.out.println("Accuracy Level parameter is equal--------"+param);
        Vector<Integer> myvect=new Vector<Integer>();
        if(param.equalsIgnoreCase("width_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where width_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("length_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where length_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                //System.out.println("222");
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("depth_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where depth_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("pages_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where pages_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("weight_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where weight_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("format_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where format_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("mailformat_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where mailformat_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("items_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where items_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        if(param.equalsIgnoreCase("prodcode_by"))
        {
            try
            {
                PreparedStatement pstmt = c.prepareStatement("select al_id from accuracy_levels where prodcode_by=?") ;
                pstmt.setInt(1,acclevel);
                ResultSet rset=pstmt.executeQuery();
                while(rset.next())
                {   
                    myvect.add(rset.getInt(1));
                }
            }
            catch(SQLException ex1)
            {
                ex1.printStackTrace();
            }
        }
        return myvect;
    }

    
    //update Accuracy Level
    public void update(AccuracyLevels aculevel) {
        try {
            PreparedStatement ps = c.prepareStatement("update accuracy_levels set width_by=? , pages_by=? , depth_by=? , length_by=? , weight_by=? , format_by=? , mail_format_by=? , item_by=? ,prodcode_by=? where al_id=?");
            ps.setInt(1, aculevel.getWidthBy());
            ps.setInt(2, aculevel.getPagesBy());
            ps.setInt(3, aculevel.getDepthBy());
            ps.setInt(4, aculevel.getLengthBy());
            ps.setInt(5, aculevel.getWeightBy());
            ps.setInt(6, aculevel.getFormatBy());
            ps.setInt(7, aculevel.getMailFormatBy());
            ps.setInt(8, aculevel.getItemBy());
            ps.setInt(9, aculevel.getProdCodeBy());
            ps.setInt(10, aculevel.getAlId());
            
            ps.executeUpdate();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    //method to get Accuracy Levels by ID
    public AccuracyLevels getById(int alId) {
        try {
            AccuracyLevels al = new AccuracyLevels();
            PreparedStatement ps = c.prepareStatement("select * from accuracy_levels where al_id=?");
            ps.setInt(1, alId);
            ResultSet rset = ps.executeQuery();
            rset.next();
            al.setAlId(rset.getInt(1));
            al.setWidthBy(rset.getInt(2));
            al.setPagesBy(rset.getInt(3));
            al.setDepthBy(rset.getInt(4));
            al.setLengthBy(rset.getInt(5));
            al.setWeightBy(rset.getInt(6));
            al.setFormatBy(rset.getInt(7));
            al.setMailFormatBy(rset.getInt(8));
            al.setItemBy(rset.getInt(9));
            al.setProdCodeBy(rset.getInt(10));
            return al;
        } catch (SQLException ex) {
            Logger.getLogger(ProviderDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
